{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Session Connection\n",
    "\n",
    "* Connects to snowflake session\n",
    "* Section delineates creating our required warehouse, database, schema and stage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from snowflake.snowpark import Session\n",
    "import configparser\n",
    "\n",
    "# In this case I'm loading my credentials as a dictionary\n",
    "snowflake_credentials_file = '../snowflake_creds.config'\n",
    "config = configparser.ConfigParser()\n",
    "config.read(snowflake_credentials_file)\n",
    "connection_parameters = dict(config['default'])\n",
    "\n",
    "session = Session.builder.configs(connection_parameters).create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(CURRENT_WAREHOUSE()='COMPUTE_WH', CURRENT_DATABASE()='SNOWPARK_DEFINITIVE_GUIDE', CURRENT_SCHEMA()='MY_SCHEMA', CURRENT_USER()='PRESTONB', CURRENT_ROLE()='ACCOUNTADMIN')]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.sql(\"CREATE WAREHOUSE IF NOT EXISTS COMPUTE_WH WITH WAREHOUSE_SIZE='X-SMALL'\").collect()\n",
    "session.sql(\"CREATE DATABASE IF NOT EXISTS SNOWPARK_DEFINITIVE_GUIDE\").collect()\n",
    "session.sql(\"CREATE SCHEMA IF NOT EXISTS SNOWPARK_DEFINITIVE_GUIDE.MY_SCHEMA\").collect()\n",
    "session.sql(\"CREATE STAGE IF NOT EXISTS SNOWPARK_DEFINITIVE_GUIDE.MY_SCHEMA.MY_STAGE\").collect()\n",
    "\n",
    "session.use_database(\"SNOWPARK_DEFINITIVE_GUIDE\")\n",
    "session.use_schema(\"MY_SCHEMA\")\n",
    "\n",
    "session.sql('select current_warehouse(), current_database(), current_schema(), current_user(), current_role()').collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loading CSV Format\n",
    "\n",
    "* This section explains loading required CSV files into the Snowflake Instance\n",
    "* Steps involved uploading the CSV files to Snowflake stage, defining schema and loading into table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Uploading Files To Stage Location"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[PutResult(source='marketing_additional.csv', target='marketing_additional.csv.gz', source_size=23683, target_size=0, source_compression='NONE', target_compression='GZIP', status='SKIPPED', message='')]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.file.put('../datasets/purchase_history.csv', 'MY_STAGE')\n",
    "session.file.put('../datasets/marketing_additional.csv', 'MY_STAGE')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Defining Schema and Loading Datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "|\"ID\"  |\"YEAR_BIRTH\"  |\"EDUCATION\"  |\"MARITAL_STATUS\"  |\"INCOME\"  |\"KIDHOME\"  |\"TEENHOME\"  |\"DT_CUSTOMER\"  |\"RECENCY\"  |\"MNTWINES\"  |\"MNTFRUITS\"  |\"MNTMEATPRODUCTS\"  |\"MNTFISHPRODUCTS\"  |\"MNTSWEETPRODUCTS\"  |\"MNTGOLDPRODS\"  |\"NUMDEALSPURCHASES\"  |\"NUMWEBPURCHASES\"  |\"NUMCATALOGPURCHASES\"  |\"NUMSTOREPURCHASES\"  |\"NUMWEBVISITSMONTH\"  |\n",
      "-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "|5524  |1957          |Graduation   |Single            |58138     |0          |0           |2012-09-04     |58         |635         |88           |546                |172                |88                  |88              |3                    |8                  |10                     |4                    |7                    |\n",
      "|2174  |1954          |Graduation   |Single            |46344     |1          |1           |2014-03-08     |38         |11          |1            |6                  |2                  |1                   |6               |2                    |1                  |1                      |2                    |5                    |\n",
      "|4141  |1965          |Graduation   |Together          |71613     |0          |0           |2013-08-21     |26         |426         |49           |127                |111                |21                  |42              |1                    |8                  |2                      |10                   |4                    |\n",
      "|6182  |1984          |Graduation   |Together          |26646     |1          |0           |2014-02-10     |26         |11          |4            |20                 |10                 |3                   |5               |2                    |2                  |0                      |4                    |6                    |\n",
      "|5324  |1981          |PhD          |Married           |58293     |1          |0           |2014-01-19     |94         |173         |43           |118                |46                 |27                  |15              |5                    |5                  |3                      |6                    |5                    |\n",
      "|7446  |1967          |Master       |Together          |62513     |0          |1           |2013-09-09     |16         |520         |42           |98                 |0                  |42                  |14              |2                    |6                  |4                      |10                   |6                    |\n",
      "|965   |1971          |Graduation   |Divorced          |55635     |0          |1           |2012-11-13     |34         |235         |65           |164                |50                 |49                  |27              |4                    |7                  |3                      |7                    |6                    |\n",
      "|6177  |1985          |PhD          |Married           |33454     |1          |0           |2013-05-08     |32         |76          |10           |56                 |3                  |1                   |23              |2                    |4                  |0                      |4                    |8                    |\n",
      "|4855  |1974          |PhD          |Together          |30351     |1          |0           |2013-06-06     |19         |14          |0            |24                 |3                  |3                   |2               |1                    |3                  |0                      |2                    |9                    |\n",
      "|5899  |1950          |PhD          |Together          |5648      |1          |1           |2014-03-13     |68         |28          |0            |6                  |1                  |1                   |13              |1                    |1                  |0                      |0                    |20                   |\n",
      "-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "import snowflake.snowpark.types as T\n",
    "\n",
    "purchase_history_schema = T.StructType([T.StructField(\"ID\", T.IntegerType()),\n",
    "        T.StructField(\"Year_Birth\", T.IntegerType()),T.StructField(\"Education\", T.StringType()),\n",
    "        T.StructField(\"Marital_Status\", T.StringType()),T.StructField(\"Income\", T.IntegerType()),\n",
    "        T.StructField(\"Kidhome\", T.IntegerType()),T.StructField(\"Teenhome\", T.IntegerType()),\n",
    "        T.StructField(\"Dt_Customer\", T.DateType()),T.StructField(\"Recency\", T.IntegerType()),\n",
    "        T.StructField(\"MntWines\", T.IntegerType()),T.StructField(\"MntFruits\", T.IntegerType()),\n",
    "        T.StructField(\"MntMeatProducts\", T.IntegerType()),T.StructField(\"MntFishProducts\", T.IntegerType()),\n",
    "        T.StructField(\"MntSweetProducts\", T.IntegerType()),T.StructField(\"MntGoldProds\", T.IntegerType()),\n",
    "        T.StructField(\"NumDealsPurchases\", T.IntegerType()),T.StructField(\"NumWebPurchases\", T.IntegerType()),\n",
    "        T.StructField(\"NumCatalogPurchases\", T.IntegerType()),T.StructField(\"NumStorePurchases\", T.IntegerType()),\n",
    "        T.StructField(\"NumWebVisitsMonth\", T.IntegerType())\n",
    "])\n",
    "\n",
    "session.sql(\"TRUNCATE TABLE IF EXISTS PURCHASE_HISTORY\").collect()\n",
    "\n",
    "purchase_history = session.read\\\n",
    "        .option(\"FIELD_DELIMITER\", ',')\\\n",
    "        .option(\"SKIP_HEADER\", 1)\\\n",
    "        .option(\"ON_ERROR\", \"CONTINUE\")\\\n",
    "        .schema(purchase_history_schema).csv(\"@MY_Stage/purchase_history.csv.gz\")\\\n",
    "        .copy_into_table(\"PURCHASE_HISTORY\")\n",
    "\n",
    "session.table(\"PURCHASE_HISTORY\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "|\"ID\"   |\"YEAR_BIRTH\"  |\"EDUCATION\"  |\"MARITAL_STATUS\"  |\"INCOME\"  |\"KIDHOME\"  |\"TEENHOME\"  |\"DT_CUSTOMER\"  |\"RECENCY\"  |\"MNTWINES\"  |\"MNTFRUITS\"  |\"MNTMEATPRODUCTS\"  |\"MNTFISHPRODUCTS\"  |\"MNTSWEETPRODUCTS\"  |\"MNTGOLDPRODS\"  |\"NUMDEALSPURCHASES\"  |\"NUMWEBPURCHASES\"  |\"NUMCATALOGPURCHASES\"  |\"NUMSTOREPURCHASES\"  |\"NUMWEBVISITSMONTH\"  |\"ACCEPTEDCMP3\"  |\"ACCEPTEDCMP4\"  |\"ACCEPTEDCMP5\"  |\"ACCEPTEDCMP1\"  |\"ACCEPTEDCMP2\"  |\"COMPLAIN\"  |\"Z_COSTCONTACT\"  |\"Z_REVENUE\"  |\"RESPONSE\"  |\n",
      "-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "|4860   |1970          |Graduation   |Single            |24206     |1          |0           |2013-03-08     |66         |7           |2            |8                  |3                  |2                   |3               |1                    |1                  |0                      |3                    |6                    |0               |0               |0               |0               |0               |0           |3                |11           |0           |\n",
      "|10757  |1967          |PhD          |Divorced          |28420     |1          |0           |2013-12-24     |36         |4           |2            |5                  |2                  |0                   |0               |1                    |1                  |0                      |2                    |6                    |0               |0               |0               |0               |0               |0           |3                |11           |0           |\n",
      "|4023   |1970          |Graduation   |Married           |22979     |1          |0           |2012-09-06     |29         |16          |17           |19                 |20                 |21                  |22              |3                    |3                  |2                      |2                    |8                    |0               |0               |0               |0               |0               |0           |3                |11           |1           |\n",
      "|6679   |1966          |Graduation   |Single            |33279     |0          |0           |2014-06-12     |29         |10          |3            |3                  |0                  |0                   |0               |1                    |0                  |0                      |3                    |3                    |0               |0               |0               |0               |0               |0           |3                |11           |0           |\n",
      "|9923   |1972          |Master       |Single            |46423     |1          |1           |2013-09-18     |6          |68          |0            |16                 |0                  |0                   |8               |3                    |2                  |0                      |4                    |7                    |0               |0               |0               |0               |0               |0           |3                |11           |0           |\n",
      "|7181   |1977          |Graduation   |Married           |30368     |0          |1           |2013-11-07     |97         |35          |0            |13                 |2                  |0                   |2               |2                    |2                  |0                      |3                    |8                    |0               |0               |0               |0               |0               |0           |3                |11           |0           |\n",
      "|3599   |1970          |Graduation   |Married           |63684     |0          |1           |2012-11-26     |61         |575         |80           |428                |208                |93                  |80              |3                    |8                  |4                      |8                    |5                    |0               |0               |0               |0               |0               |0           |3                |11           |0           |\n",
      "|405    |1964          |Graduation   |Divorced          |41638     |0          |1           |2013-02-13     |68         |315         |0            |31                 |4                  |0                   |91              |4                    |5                  |5                      |3                    |8                    |1               |0               |0               |0               |0               |0           |3                |11           |0           |\n",
      "|6870   |1959          |PhD          |Divorced          |68805     |0          |1           |2013-09-17     |73         |182         |2            |49                 |17                 |13                  |20              |1                    |3                  |1                      |7                    |2                    |0               |0               |0               |0               |0               |0           |3                |11           |0           |\n",
      "|832    |1966          |PhD          |Together          |65814     |0          |1           |2014-03-02     |90         |561         |14           |113                |10                 |14                  |35              |3                    |8                  |2                      |10                   |5                    |0               |0               |0               |0               |0               |0           |3                |11           |0           |\n",
      "-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "import snowflake.snowpark.types as T\n",
    "\n",
    "marketing_additional_schema = T.StructType([T.StructField(\"ID\", T.IntegerType()),\n",
    "        T.StructField(\"Year_Birth\", T.IntegerType()),T.StructField(\"Education\", T.StringType()),\n",
    "        T.StructField(\"Marital_Status\", T.StringType()),T.StructField(\"Income\", T.IntegerType()),\n",
    "        T.StructField(\"Kidhome\", T.IntegerType()),T.StructField(\"Teenhome\", T.IntegerType()),\n",
    "        T.StructField(\"Dt_Customer\", T.DateType()),T.StructField(\"Recency\", T.IntegerType()),\n",
    "        T.StructField(\"MntWines\", T.IntegerType()),T.StructField(\"MntFruits\", T.IntegerType()),\n",
    "        T.StructField(\"MntMeatProducts\", T.IntegerType()),T.StructField(\"MntFishProducts\", T.IntegerType()),\n",
    "        T.StructField(\"MntSweetProducts\", T.IntegerType()),T.StructField(\"MntGoldProds\", T.IntegerType()),\n",
    "        T.StructField(\"NumDealsPurchases\", T.IntegerType()),T.StructField(\"NumWebPurchases\", T.IntegerType()),\n",
    "        T.StructField(\"NumCatalogPurchases\", T.IntegerType()),T.StructField(\"NumStorePurchases\", T.IntegerType()),\n",
    "        T.StructField(\"NumWebVisitsMonth\", T.IntegerType()),T.StructField(\"AcceptedCmp3\", T.IntegerType()),\n",
    "        T.StructField(\"AcceptedCmp4\", T.IntegerType()),T.StructField(\"AcceptedCmp5\", T.IntegerType()),\n",
    "        T.StructField(\"AcceptedCmp1\", T.IntegerType()),T.StructField(\"AcceptedCmp2\", T.IntegerType()),\n",
    "        T.StructField(\"Complain\", T.IntegerType()),T.StructField(\"Z_CostContact\", T.IntegerType()),\n",
    "        T.StructField(\"Z_Revenue\", T.IntegerType()),T.StructField(\"Response\", T.DecimalType())\n",
    "])\n",
    "\n",
    "session.sql(\"TRUNCATE TABLE IF EXISTS MARKETING_ADDITIONAL\").collect()\n",
    "\n",
    "marketing_additional = session.read\\\n",
    "        .option(\"FIELD_DELIMITER\", ',')\\\n",
    "        .option(\"SKIP_HEADER\", 1)\\\n",
    "        .option(\"ON_ERROR\", \"CONTINUE\")\\\n",
    "        .schema(marketing_additional_schema).csv(\"@MY_Stage/marketing_additional.csv.gz\")\\\n",
    "        .copy_into_table(\"MARKETING_ADDITIONAL\")\n",
    "\n",
    "session.table(\"MARKETING_ADDITIONAL\").show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loading JSON Format\n",
    "\n",
    "* This section explains loading required JSON file into the Snowflake Instance\n",
    "* Steps involved uploading the JSON file to Snowflake stage, defining schema and loading into table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------\n",
      "|\"$1\"                  |\n",
      "------------------------\n",
      "|{                     |\n",
      "|  \"AcceptedCmp1\": 0,  |\n",
      "|  \"AcceptedCmp2\": 0,  |\n",
      "|  \"AcceptedCmp3\": 0,  |\n",
      "|  \"AcceptedCmp4\": 0,  |\n",
      "|  \"AcceptedCmp5\": 0,  |\n",
      "|  \"ID\": 5524,         |\n",
      "|  \"Response\": 1       |\n",
      "|}                     |\n",
      "|{                     |\n",
      "|  \"AcceptedCmp1\": 0,  |\n",
      "|  \"AcceptedCmp2\": 0,  |\n",
      "|  \"AcceptedCmp3\": 0,  |\n",
      "|  \"AcceptedCmp4\": 0,  |\n",
      "|  \"AcceptedCmp5\": 0,  |\n",
      "|  \"ID\": 2174,         |\n",
      "|  \"Response\": 0       |\n",
      "|}                     |\n",
      "|{                     |\n",
      "|  \"AcceptedCmp1\": 0,  |\n",
      "|  \"AcceptedCmp2\": 0,  |\n",
      "|  \"AcceptedCmp3\": 0,  |\n",
      "|  \"AcceptedCmp4\": 0,  |\n",
      "|  \"AcceptedCmp5\": 0,  |\n",
      "|  \"ID\": 4141,         |\n",
      "|  \"Response\": 0       |\n",
      "|}                     |\n",
      "|{                     |\n",
      "|  \"AcceptedCmp1\": 0,  |\n",
      "|  \"AcceptedCmp2\": 0,  |\n",
      "|  \"AcceptedCmp3\": 0,  |\n",
      "|  \"AcceptedCmp4\": 0,  |\n",
      "|  \"AcceptedCmp5\": 0,  |\n",
      "|  \"ID\": 6182,         |\n",
      "|  \"Response\": 0       |\n",
      "|}                     |\n",
      "|{                     |\n",
      "|  \"AcceptedCmp1\": 0,  |\n",
      "|  \"AcceptedCmp2\": 0,  |\n",
      "|  \"AcceptedCmp3\": 0,  |\n",
      "|  \"AcceptedCmp4\": 0,  |\n",
      "|  \"AcceptedCmp5\": 0,  |\n",
      "|  \"ID\": 5324,         |\n",
      "|  \"Response\": 0       |\n",
      "|}                     |\n",
      "|{                     |\n",
      "|  \"AcceptedCmp1\": 0,  |\n",
      "|  \"AcceptedCmp2\": 0,  |\n",
      "|  \"AcceptedCmp3\": 0,  |\n",
      "|  \"AcceptedCmp4\": 0,  |\n",
      "|  \"AcceptedCmp5\": 0,  |\n",
      "|  \"ID\": 7446,         |\n",
      "|  \"Response\": 0       |\n",
      "|}                     |\n",
      "|{                     |\n",
      "|  \"AcceptedCmp1\": 0,  |\n",
      "|  \"AcceptedCmp2\": 0,  |\n",
      "|  \"AcceptedCmp3\": 0,  |\n",
      "|  \"AcceptedCmp4\": 0,  |\n",
      "|  \"AcceptedCmp5\": 0,  |\n",
      "|  \"ID\": 965,          |\n",
      "|  \"Response\": 0       |\n",
      "|}                     |\n",
      "|{                     |\n",
      "|  \"AcceptedCmp1\": 0,  |\n",
      "|  \"AcceptedCmp2\": 0,  |\n",
      "|  \"AcceptedCmp3\": 0,  |\n",
      "|  \"AcceptedCmp4\": 0,  |\n",
      "|  \"AcceptedCmp5\": 0,  |\n",
      "|  \"ID\": 6177,         |\n",
      "|  \"Response\": 0       |\n",
      "|}                     |\n",
      "|{                     |\n",
      "|  \"AcceptedCmp1\": 0,  |\n",
      "|  \"AcceptedCmp2\": 0,  |\n",
      "|  \"AcceptedCmp3\": 0,  |\n",
      "|  \"AcceptedCmp4\": 0,  |\n",
      "|  \"AcceptedCmp5\": 0,  |\n",
      "|  \"ID\": 4855,         |\n",
      "|  \"Response\": 1       |\n",
      "|}                     |\n",
      "|{                     |\n",
      "|  \"AcceptedCmp1\": 0,  |\n",
      "|  \"AcceptedCmp2\": 0,  |\n",
      "|  \"AcceptedCmp3\": 1,  |\n",
      "|  \"AcceptedCmp4\": 0,  |\n",
      "|  \"AcceptedCmp5\": 0,  |\n",
      "|  \"ID\": 5899,         |\n",
      "|  \"Response\": 0       |\n",
      "|}                     |\n",
      "------------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "session.file.put('../datasets/campaign_info.json', 'MY_STAGE')\n",
    "\n",
    "df_from_json = session.read.json(\"@My_Stage/campaign_info.json.gz\")\n",
    "df_from_json.write.save_as_table(\"CAMPAIGN_INFO_TEMP\", mode = \"overwrite\")\n",
    "\n",
    "df_from_json.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------------------------------------------------------------------------------------------------\n",
      "|\"ID\"  |\"ACCEPTEDCMP1\"  |\"ACCEPTEDCMP2\"  |\"ACCEPTEDCMP3\"  |\"ACCEPTEDCMP4\"  |\"ACCEPTEDCMP5\"  |\"RESPONSE\"  |\n",
      "----------------------------------------------------------------------------------------------------------\n",
      "|5524  |0               |0               |0               |0               |0               |1           |\n",
      "|2174  |0               |0               |0               |0               |0               |0           |\n",
      "|4141  |0               |0               |0               |0               |0               |0           |\n",
      "|6182  |0               |0               |0               |0               |0               |0           |\n",
      "|5324  |0               |0               |0               |0               |0               |0           |\n",
      "|7446  |0               |0               |0               |0               |0               |0           |\n",
      "|965   |0               |0               |0               |0               |0               |0           |\n",
      "|6177  |0               |0               |0               |0               |0               |0           |\n",
      "|4855  |0               |0               |0               |0               |0               |1           |\n",
      "|5899  |0               |0               |1               |0               |0               |0           |\n",
      "----------------------------------------------------------------------------------------------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "from snowflake.snowpark.functions import col\n",
    "\n",
    "df_flatten = df_from_json.select(col(\"$1\")[\"ID\"].as_(\"ID\"),\\\n",
    "    col(\"$1\")[\"AcceptedCmp1\"].as_(\"AcceptedCmp1\"),\\\n",
    "    col(\"$1\")[\"AcceptedCmp2\"].as_(\"AcceptedCmp2\"),\\\n",
    "    col(\"$1\")[\"AcceptedCmp3\"].as_(\"AcceptedCmp3\"),\\\n",
    "    col(\"$1\")[\"AcceptedCmp4\"].as_(\"AcceptedCmp4\"),\\\n",
    "    col(\"$1\")[\"AcceptedCmp5\"].as_(\"AcceptedCmp5\"),\\\n",
    "    col(\"$1\")[\"Response\"].as_(\"Response\"))\n",
    "\n",
    "df_flatten.write.save_as_table(\"CAMPAIGN_INFO\", mode=\"overwrite\")\n",
    "session.table(\"CAMPAIGN_INFO\").show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loading Parquet Format\n",
    "\n",
    "* This section explains loading required parquet file into the Snowflake Instance\n",
    "* Steps involved uploading the parquet file to Snowflake stage, defining schema and loading into table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-----------------------------------------------------\n",
      "|\"ID\"  |\"COMPLAIN\"  |\"Z_COSTCONTACT\"  |\"Z_REVENUE\"  |\n",
      "-----------------------------------------------------\n",
      "|5524  |0           |3                |11           |\n",
      "|2174  |0           |3                |11           |\n",
      "|4141  |0           |3                |11           |\n",
      "|6182  |0           |3                |11           |\n",
      "|5324  |0           |3                |11           |\n",
      "|7446  |0           |3                |11           |\n",
      "|965   |0           |3                |11           |\n",
      "|6177  |0           |3                |11           |\n",
      "|4855  |0           |3                |11           |\n",
      "|5899  |0           |3                |11           |\n",
      "-----------------------------------------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "session.file.put('../datasets/complain_info.parquet', 'MY_STAGE')\n",
    "\n",
    "session.sql(\"TRUNCATE TABLE IF EXISTS COMPLAINT_INFO\").collect()\n",
    "\n",
    "df_raw = session.read.parquet(\"@My_Stage/complain_info.parquet\")\n",
    "df_raw.copy_into_table(\"COMPLAINT_INFO\")\n",
    "\n",
    "session.table(\"COMPLAINT_INFO\").show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loading Images\n",
    "\n",
    "* This section explains loading required image files into the Snowflake Instance\n",
    "* Steps involved uploading the image file to Snowflake stage and reading a image file\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(status='MY_IMAGES already exists, statement succeeded.')]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.sql(\"CREATE STAGE IF NOT EXISTS SNOWPARK_DEFINITIVE_GUIDE.My_Schema.My_Images DIRECTORY = ( ENABLE = TRUE )\").collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[PutResult(source='100.png', target='100.png.gz', source_size=2466, target_size=0, source_compression='NONE', target_compression='GZIP', status='SKIPPED', message=''),\n",
       " PutResult(source='1007.png', target='1007.png.gz', source_size=2246, target_size=0, source_compression='NONE', target_compression='GZIP', status='SKIPPED', message=''),\n",
       " PutResult(source='101.png', target='101.png.gz', source_size=3039, target_size=0, source_compression='NONE', target_compression='GZIP', status='SKIPPED', message='')]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.file.put(\"../datasets/sample_images/*.png\", \"@My_Images\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-----------------------------------------------------------------------------------------------------\n",
      "|\"name\"                 |\"size\"  |\"md5\"                             |\"last_modified\"                |\n",
      "-----------------------------------------------------------------------------------------------------\n",
      "|my_images/100.png.gz   |2512    |632f73fb91a6f07ecfc31358de6775a7  |Thu, 11 Jan 2024 04:08:06 GMT  |\n",
      "|my_images/1007.png.gz  |2288    |073f05363aeb16ab317ca5791d1b0e7a  |Thu, 11 Jan 2024 04:08:06 GMT  |\n",
      "|my_images/101.png.gz   |3040    |1dc68dc50c225fc6d697d79798885daf  |Thu, 11 Jan 2024 04:08:06 GMT  |\n",
      "-----------------------------------------------------------------------------------------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "session.sql(\"LS @My_Images\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAJYAAACWCAMAAAAL34HQAAAAYFBMVEX///8ptegAr+Ybs+cAsOcTsueN0fC14PX5/f72+/7C5fdHvOprxu3V7fkArea94/ba7/rl9PvN6vib1vLu+P3g8vul2vOAze+t3fRvx+1kxOxYwOs3uOmX1fKDzu/J6PjiZxtEAAAHsklEQVR4Ae1ZaXPjKhBkBNjxFctHbMvn//+Xr3sGdGUr8W7tc/YDVCWSYGCanoMhca60wkBhoDBQGCgMFAYKA4WBwkBhoDBQGCgMFAYKA4WBwkBhoDBQGCgMFAYKA4WBwsALGFglHfn5ApVPqKhEHhDbilRPSL9MREQWULbA8+1lSr9VVAMOaQJp8g+ZcdWDtf12Ey8TKLCepHpDuU9sae+TK/wfYpNw/gWsc5j8H8qeXpMh6A8jtg7+LwXksfLOxekYzfU+7hl/HwBAwmOQIB6BnYex6O9/H71EwAK0YYvvw+/PXwpLwhQ4ct6aKqq/AStIxKJexmrZ+3XbkBiRiJ8Mi+9oH19PfGJ0JuGAE+1PYGleNxwZVvf1hOqvRE6y5fAfwTolcjq2FFY8faXwubGjWe+PYLl7xtVny38bK08gu8Df0Z6HVS96ZJyZDtB6sPzlCa3fiiwsBJ+HdXG73qLXT7CuvdE/f/1dWG/u5tZUl6ON9UzHFmljy6POzY/3mXYh968mu64i294ntQ7c9Hf7a713j8nV33hWZLamR06cYtWcIPa2/Uda5OSaPYu+bUgI9sTFngvh7W31EFivsjXBe+2tozsEH6t8Yk4wENQfwjD7vl/dO3zDe4BIsBZRJ77HDpYl2mPEIrTe1u03RzzfMZFq0RPF13h+eAlbdnA1Scl47bG+kjmXOggG0m6mmIUJNaWHdodCtQBThMFaIONzIplIbG18wMyJrh7neIVFdK3EEXqmla27qFIwsHxOmCsJ93sQ2GmOpQO2LQ2msJYN50tQkJEa2jaD8AQue2WKUFg19tVcvVwIKcEKwiohSLgsbBFZ0WIOmsWbBpcsl5+NhieFIEZT1FxrjvMAlr3zqHPuKFXtHE5Q+Onw4DsJzdx3eXAHx5oY6warFm5lIRVWxyJ0w5uiICzxrbfuzxmbuykqS4cP0XRxh0kBSxNaEOABWep8O9I1MVn0shmgPiyvG3FgsGWrUs6j5w7cVDnnm7oPcLWFBxzF+t3aUqx9HtKlAxhgRJX4YHzgyDPxALkPUobGPQOwivVgHW07VJlhPXT6XJJTVt2+Un6PdtfZw/eMrlUeUB07tYi+zrO70QwToZuiHfni6SduYkjNSj1YrLu0qRPpcKVmu4plHtDVWs0hrrRBiQMqRBlxWQxZhHFAvDFBtuAHbNQYElnmwkZQEEb41s7oHix1bk58y2xVVjm1Buot57K1aOUocYMCCVnKoMaMhbFCZWjzfLddAlZyF3QTYKN8WB5pzCAdrL20Fao3Ix6SL8llnVo0/1A9bme+XbkZo74hXUZWFwmaWYIabJ4dQGEtbQnAwooPRsFWFKsx14vEbecHNCB+quSL4kNqyW3TmkflJmJZbtcjuMyxEj8qtUBXpOmHsNoqQ6fSqZdyIfB0eHRsrbMHmcFjdRO1Nhyka322ksk+w0rpTGG5W4UcrZFo307ZGsJiyq7iXN1fs2KPrTEsj32qZ+5RVufWhzUxbtSIZ3cWP0tGjBbqCcb+HOkMQ7Za5DSiq6AH1kGSy57UsfUYGRHZK2gcv3VOlzTpI9emMBnsVOOMYw43u6ZTKInDCd9GsNTJOawxSRCyJLJdCowO1iZ5EmTVt/BXq7X5fM5nSYs9tilBkFD4PFAxi+TO7UD2KqchrGGCcCeZTZEYkKFycutguXSIYkFNEPrL4zRyXYLolGX9G+0CLkXlHBKFNjV/K46lB0Y8WnZiXtVc4hc05kpOucDqwWrTqfqCneuaEWgDa93l0ZJDW1bhgE8idoHNu7bOGll5AGufD5+kM0QtVPxCdjajB6vRlI7u7vBBxENuqmUeBnb5he6Ehqt+arMjTahNr/tiKDep5AE5A1gueD21cmbEJhkmYNrY70Vie36iXLK8BcGNepdPB16Qvak2WD4lqI/UmZ53ZVL7DnrhmNILhrAarUlQQVpwIBcRj4aNTuyxhfDm5nb9wgZ9UAacxHNVjDoNjEq+4xyCUYOLsI1dOKivM1TMZ2TUcYIgMWFpmYOCG5O/Zdv22XIPFMYNFlloJGIptAMnzLDwklV5F2A4ZlJZMccpTYuscF6nugCl0bvOdtCMXcZ6dCZiXat3kWe1qWsha+VIyWzp7rCcUtUrmq125N9PQEECwoXqdHi5D4YeUzNL5ZDsGCt1HfSesRlNrw/jD7WMqeYu9VTiajCKPvK57i4mFCznS4we1SyzbmVsuYUKnEOM0abqfNQs9pzp6UxZe0men0bR/TgejRItqtAxSx65bpqO/J3tp8khv7FjYEKPQzss71hxh73yh+3NBNbNcmcdg9+MR7QWVnaqgdDLP2g4th6sLoe8HE1WaHecEax8H8pCL3/iCE6tz5b4X1n7heDS8TdiK5VDL8QxVPVhVLHqymxZBaY3waHsC7/sT7oV7qUtrLUV8+1R+UI0rSqFFVd1D9bKrok/CotGxLV6+M8V/CVGftaIblcx1Q5h4dZX/XAk8o9Kn2FZb2vqn3oZs/VTOEZ6C6wRIV9+DhLEl5KvHURGyGXgaxV/rQ2ZPRXNPIP+nZYq5d7/CP4dbAVJYaAwUBgoDBQGCgOFgcJAYaAwUBgoDBQGCgOFgcJAYaAwUBgoDBQGCgOFgcJAYeBvMPAfa3U5GtezoisAAAAASUVORK5CYII=",
      "text/plain": [
       "<PIL.Image.Image image mode=P size=150x150>"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "import PIL.Image\n",
    "\n",
    "bytes_object = session.file.get_stream(\n",
    "    \"@My_Images/101.png.gz\", decompress=True)\n",
    "image = PIL.Image.open(bytes_object)\n",
    "image.resize((150,150))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Dynamically Reading Multiple Files\n",
    "\n",
    "* This section defines UDF that aids in working with multiple image files at once\n",
    "* Also provides execution methodology to utilize the UDF to print size of each image file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "import snowflake.snowpark as snowpark\n",
    "from snowflake.snowpark.functions import udf\n",
    "from snowflake.snowpark.files import SnowflakeFile\n",
    "from snowflake.snowpark.types import StringType, IntegerType\n",
    "\n",
    "@udf(\n",
    "    name=\"get_bytes_length\",\n",
    "    replace=True,\n",
    "    input_types=[StringType()],\n",
    "    return_type=IntegerType(),\n",
    "    packages=['snowflake-snowpark-python']\n",
    ")\n",
    "def get_file_length(file_path):\n",
    "  with SnowflakeFile.open(file_path, mode = 'rb') as f:\n",
    "    s = f.read()\n",
    "    return len(s)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(RELATIVE_PATH='100.png.gz', SIZE=2499),\n",
       " Row(RELATIVE_PATH='1007.png.gz', SIZE=2280),\n",
       " Row(RELATIVE_PATH='101.png.gz', SIZE=3028)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.sql(\"\"\"SELECT RELATIVE_PATH, get_bytes_length(build_scoped_file_url(@MY_TEXTS,RELATIVE_PATH))\n",
    "             as SIZE from DIRECTORY(@MY_TEXTS);\"\"\").collect()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
